﻿Imports MySql.Data.MySqlClient

Public Class onlineCheckIn

    Public Sub insert()
        Dim adults As Integer = 0
        Dim children As Integer = 0
        Dim checkIn As String = ""
        Dim checkOut As String = ""
        Dim firstName As String = ""
        Dim lastName As String = ""
        Dim contact As Long = 0
        Dim email As String = ""
        Dim creditCard As Long = 0
        Dim roomNo As String = userDetailsForm.roomNoTxt.Text
        Dim roomType As Integer = 0
        Dim connString As String = "Server=localhost; User Id=root; Password=; Database=leximoore"
        Dim dr As MySqlDataReader
        Dim code As String = bookedOnlineForm.bookingCodeTxt.Text()
        If (userDetailsForm.roomTypeTxt.Text = "President Suite") Then
            roomType = 1
        Else
            roomType = 2
        End If

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "SELECT * FROM reservation WHERE confirmation = '" + code + "'"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                End With
                Try
                    SQLConnection.Open()
                    dr = sqlCommand.ExecuteReader()
                    While (dr.Read())
                        firstName = dr.GetString(1)
                        lastName = dr.GetString(2)
                        email = dr.GetString(7)
                        contact = dr.GetString(8)
                        checkIn = dr.GetString(11)
                        checkOut = dr.GetString(12)
                        adults = dr.GetString(13)
                        children = dr.GetString(14)
                        code = dr.GetString(20)
                        creditCard = dr.GetString(21)
                    End While
                    SQLConnection.Close()
                    SQLConnection.Open()
                    With sqlCommand
                        Dim noRoom As Integer = 1
                        .CommandText = "INSERT INTO reservation (firstname, lastname, email, contact, arrival, departure, adults, child, room_id, no_room, credit_card, confirmation) values (@firstName,@lastName,@email,@contact,@checkIn,@checkOut,@adults,@children,@roomType,@noRoom,@creditCard,@code)"
                        .Connection = SQLConnection
                        .CommandType = CommandType.Text
                        .Parameters.AddWithValue("@adults", adults)
                        .Parameters.AddWithValue("@children", children)
                        .Parameters.AddWithValue("@checkIn", checkIn)
                        .Parameters.AddWithValue("@checkOut", checkOut)
                        .Parameters.AddWithValue("@firstName", firstName)
                        .Parameters.AddWithValue("@lastName", lastName)
                        .Parameters.AddWithValue("@contact", contact)
                        .Parameters.AddWithValue("@email", email)
                        .Parameters.AddWithValue("@creditCard", creditCard)
                        .Parameters.AddWithValue("@noRoom", noRoom)
                        .Parameters.AddWithValue("@roomType", roomType)
                        .Parameters.AddWithValue("@code", code)
                        .Parameters.AddWithValue("@roomNo", roomNo)
                        .Parameters.AddWithValue("@type", userDetailsForm.roomTypeTxt.Text)
                    End With
                    sqlCommand.ExecuteNonQuery()
                Catch ex As MySqlException
                    MessageBox.Show(ex.Message)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "SELECT * FROM roominventory WHERE confirmation = '" + code + "'"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                End With
                Try
                    SQLConnection.Open()
                    dr = sqlCommand.ExecuteReader()
                    While (dr.Read())
                        checkIn = dr.GetString(2)
                        checkOut = dr.GetString(3)
                        roomType = dr.GetString(5)
                        code = dr.GetString(6)
                    End While
                    SQLConnection.Close()
                    SQLConnection.Open()
                    With sqlCommand
                        Dim noRoom As Integer = 1
                        .CommandText = "INSERT INTO roominventory (arrival, departure, room_id, confirmation, room_no) values (@checkIn,@checkOut,@roomType,@code,@roomNo)"
                        .Connection = SQLConnection
                        .CommandType = CommandType.Text
                        .Parameters.AddWithValue("@checkIn", checkIn)
                        .Parameters.AddWithValue("@checkOut", checkOut)
                        .Parameters.AddWithValue("@roomType", roomType)
                        .Parameters.AddWithValue("@code", code)
                        .Parameters.AddWithValue("@roomNo", roomNo)
                    End With
                    sqlCommand.ExecuteNonQuery()
                Catch ex As MySqlException
                    MessageBox.Show(ex.Message)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
    End Sub

    Public Sub delete()
        Dim connString As String = "Server=localhost; User Id=root; Password=; Database=leximoore"
        Dim code As String = bookedOnlineForm.bookingCodeTxt.Text()
        Dim id As Integer = 0
        Dim dr As MySqlDataReader
        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "SELECT MIN(reservation_id) FROM reservation WHERE confirmation=@code"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@code", code)
                End With
                Try
                    SQLConnection.Open()
                    dr = sqlCommand.ExecuteReader()
                    While (dr.Read())
                        id = dr.GetString(0)
                    End While
                    SQLConnection.Close()
                    SQLConnection.Open()
                    With sqlCommand
                        .CommandText = "DELETE FROM reservation WHERE reservation_id = @id"
                        .Connection = SQLConnection
                        .CommandType = CommandType.Text
                        .Parameters.AddWithValue("@id", id)
                    End With
                    sqlCommand.ExecuteNonQuery()
                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
    End Sub

End Class
